package etf.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;

import etf.library.*;

public class JDBCUtil {
	private static Connection connection = null;

	private static String driverName = "com.mysql.jdbc.Driver";

	private static String connectUrl = "jdbc:mysql://localhost:3306/masterjdbc";
	private static String user = "jdbc";
	private static String password = "jdbc";
	
	public static String sqlAddress = "insert into address (street,house_number,city,zip_post_code,country) values (?,?,?,?,?)";
	public static String sqlEmployee = "insert into employee (first_name,last_name,age,salary,address) values (?,?,?,?,?)";
	public static String sqlAuthor = "insert into author (first_name,last_name,gender,age) values (?,?,?,?)";
	public static String sqlCategory = "insert into category (name) values (?)";
	public static String sqlBook = "insert into book (title,year,category,author) values (?,?,?,?)";
	public static String sqlMember = "insert into member (first_name,last_name,phone_number,email) values (?,?,?,?)";
	public static String sqlLibrary = "insert into library (name,phone_number,email,address) values (?,?,?,?)";
	public static String sqlRentedBooks = "insert into rented_books (member_id,book_id) values (?,?)";
	public static String sqlLibrayBooks = "insert into library_books (library_id,book_id) values (?,?)";
	public static String sqlLibraryEmployees = "insert into library_employees (library_id,employee_id) values (?,?)";
	public static String sqlLibraryMembers = "insert into library_members (library_id,member_id) values (?,?)";
	
	public static PreparedStatement insertAddress = null;
	public static PreparedStatement insertEmployee = null;
	public static PreparedStatement insertAuthor = null;
	public static PreparedStatement insertCategory = null;
	public static PreparedStatement insertBook = null;
	public static PreparedStatement insertMember = null;
	public static PreparedStatement insertLibrary = null;
	public static PreparedStatement insertRentedBooks = null;
	public static PreparedStatement insertLibraryBook = null;
	public static PreparedStatement insertLibraryEmployees = null;
	public static PreparedStatement insertLibraryMembers = null;

	public static void connect(){
		try {
			loadDriver(driverName);
			connection = getConnection(connectUrl, user, password);
			System.out.println("uspio");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public static void loadDriver(String driver) {
		try {
			Class.forName(driver);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	public static Connection getConnection (String url, String user, String password) {
		try {
			return DriverManager.getConnection(url, user, password);
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}

	public static Connection getConnection() {
		try {
			return DriverManager.getConnection(connectUrl, user, password);
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}
	  public static PreparedStatement getPreparedStatement(Connection con,String sql){
	    try  {
	    
	      return con.prepareStatement(sql);
	          
	    } catch (Exception ex)  {
	      ex.printStackTrace();
	      return null;
	    } 
	  }

	public static void clearTable(Connection connection, String tableName){
		String sql = "delete from " + tableName;
		Statement deleteAllStatement = null;
		try {
			connection.setAutoCommit(false);
			deleteAllStatement = connection.createStatement();
			deleteAllStatement.executeUpdate(sql);
			connection.commit();	
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	  
	public void clearDatabase() {
		Statement st = null;
		String tables[] = { "address", "indexed_address", "employee", "author",
				"book", "category", "library", "member" };
		String delete = "delete from ";
		try {
			for (int i = 0; i < tables.length; i++) {
				String sql = delete + tables[i];
				st = connection.createStatement();
				st.execute(sql);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}

	}
	  
	public static void prepareStatements(Connection connection){
		
		try {
			insertAddress 	= connection.prepareStatement(JDBCUtil.sqlAddress,Statement.RETURN_GENERATED_KEYS);
			insertEmployee 	= connection.prepareStatement(JDBCUtil.sqlEmployee,Statement.RETURN_GENERATED_KEYS);
			insertAuthor 	= connection.prepareStatement(JDBCUtil.sqlAuthor,Statement.RETURN_GENERATED_KEYS);
			insertCategory 	= connection.prepareStatement(JDBCUtil.sqlCategory,Statement.RETURN_GENERATED_KEYS);
			insertBook 		= connection.prepareStatement(JDBCUtil.sqlBook,Statement.RETURN_GENERATED_KEYS);
			insertMember	= connection.prepareStatement(JDBCUtil.sqlMember,Statement.RETURN_GENERATED_KEYS);
			insertLibrary = connection.prepareStatement(JDBCUtil.sqlLibrary,Statement.RETURN_GENERATED_KEYS);
			insertRentedBooks = connection.prepareStatement(JDBCUtil.sqlRentedBooks,Statement.RETURN_GENERATED_KEYS);
			insertLibraryBook = connection.prepareStatement(JDBCUtil.sqlLibrayBooks,Statement.RETURN_GENERATED_KEYS);
			insertLibraryEmployees = connection.prepareStatement(JDBCUtil.sqlLibraryEmployees,Statement.RETURN_GENERATED_KEYS);
			insertLibraryMembers = connection.prepareStatement(JDBCUtil.sqlLibraryMembers,Statement.RETURN_GENERATED_KEYS);
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		
	}

	public static void setInsertLibrary(Library library, int address) {
		try {
			insertLibrary.setString(1, library.getLibraryName());
			insertLibrary.setString(2, library.getPhoneNumber());
			insertLibrary.setString(3, library.getEmail());
			insertLibrary.setInt(4, address);
			insertLibrary.execute();

		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public static void setInsertAddress(Address address) {
		try {
			insertAddress.setString(1, address.getStreet());
			insertAddress.setInt(2, address.getHouseNumber());
			insertAddress.setString(3, address.getCity());
			insertAddress.setInt(4, address.getZipPostCode());
			insertAddress.setString(5, address.getCountry());
			insertAddress.execute();

		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public static void setInsertEmp(Employee emp, int address) {
		try {
			insertEmployee.setString(1, emp.getFirstName());
			insertEmployee.setString(2, emp.getLastName());
			insertEmployee.setInt(3, emp.getAge());
			insertEmployee.setInt(4, emp.getSalary());
			insertEmployee.setInt(5, address);
//			insertEmployee.execute();

		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	public static void setInsertAuthor(Author author) {
		try {
			insertAuthor.setString(1, author.getFirstName());
			insertAuthor.setString(2, author.getLastName());
			insertAuthor.setString(3, author.getGender());
			insertAuthor.setInt(4, author.getAge());
			insertAuthor.execute();

		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public static void setInsertBook(Book book, int author, int category) {
		try {
			insertBook.setString(1, book.getTitle());
			insertBook.setInt(2, book.getYear());				
			insertBook.setInt(3, category);
			insertBook.setInt(4, author);
//			insertBook.execute();

		} catch (Exception e) {
			e.printStackTrace();
		}
	} 
	
	public static void setInsertCategory(Category cat) {
		try {
			insertCategory.setString(1, cat.getCategoryName());
			insertCategory.execute();

		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public static void setInsertMember(Member member) {
		try {
			insertMember.setString(1, member.getFirstName());
			insertMember.setString(2, member.getLastName());
			insertMember.setInt(3, member.getPhoneNumber());
			insertMember.setString(4, member.getEmail());
//			insertMember.execute();

		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public static void setInsertRentedBooks(int memberID, int bookID) {
		try {
			insertRentedBooks.setInt(1, memberID);
			insertRentedBooks.setInt(2, bookID);
			insertRentedBooks.execute();

		} catch (Exception e) {
			e.printStackTrace();
		}
	} 
	
	public static void setInsertLibraryBooks(int libraryId, int bookId) {
		try {
			insertLibraryBook.setInt(1, libraryId);
			insertLibraryBook.setInt(2, bookId);
			insertLibraryBook.execute();

		} catch (Exception e) {
			e.printStackTrace();
		}
	} 
	
	public static void setInsertLibraryEmployees(int libraryId, int empId) {
		try {
			insertLibraryEmployees.setInt(1, libraryId);
			insertLibraryEmployees.setInt(2, empId);
			insertLibraryEmployees.execute();

		} catch (Exception e) {
			e.printStackTrace();
		}
	} 
	
	public static void setInsertLibraryMembers(int libraryId, int memberId) {
		try {
			insertLibraryMembers.setInt(1, libraryId);
			insertLibraryMembers.setInt(2, memberId);
			insertLibraryMembers.execute();

		} catch (Exception e) {
			e.printStackTrace();
		}
	} 
}
